package loginbean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class WorkFlow 
{
	private static Vector<String> queryDataBase(String query)
	{
		Vector<String> results = new Vector<String>();
		try
		{
			DBManager db = new DBManager();
			Connection conn = db.getDatabaseConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while(rs.next())
			{
				results.addElement(rs.getString(1));
			}
			conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return results;
	}
	private static void insertIntoDataBase(String query)
	{
		try
		{
			DBManager db = new DBManager();
			Connection conn = db.getDatabaseConnection();
			Statement st = conn.createStatement();
			st.execute(query);
			conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public static String getAllWebServices(int tenantid)
	{
		String result = "<SELECT name='services'>";
		String strQuery = "select webservicename from webservicetable where tenantid=1 or tenantid=" + tenantid;
		Vector<String> serviceNames = queryDataBase(strQuery);
		for(int i = 0; i < serviceNames.size(); i ++)
		{
			result += "<option>"+ serviceNames.elementAt(i);
		}
		result += "</SELECT>";
		return result;
	}
	public static String getAllWorkFlows(int tenantid) throws SQLException
	{
		String result = "<SELECT name='flows'>";
		String strQuery = "select distinct(workflowname) from workflowtable where tenantid=1 or tenantid=" + tenantid;
		Vector<String> flowNames = queryDataBase(strQuery);
		for(int i = 0; i < flowNames.size(); i ++)
		{
			result += "<option>"+ flowNames.elementAt(i);
		}
		result += "</SELECT>";
		return result;
	}
	public static Vector<String> retrieveFlowServices(int tenantid, String workFlowName)
	{
		String strQuery = "select webservicename from workflowtable,webservicetable where (workflowtable.tenantid=1 or workflowtable.tenantid=" + tenantid;
		strQuery += ") and workflowtable.webserviceid=webservicetable.webserviceid and workflowname='" + workFlowName + "'";
		Vector<String> serviceNames = queryDataBase(strQuery);
		return serviceNames;
	}
	public static Vector<Integer> retrieveFlowOrder(int tenantid, String workFlowName)
	{
		String strQuery = "select wsnumber from workflowtable where (tenantid=1 or tenantid=" + tenantid;
		strQuery += ") and workflowname='" + workFlowName + "'";
		Vector<String> serviceOrder = queryDataBase(strQuery);
		Vector<Integer> result = new Vector<Integer>();
		for(int i = 0; i < serviceOrder.size(); i ++)
		{
			result.addElement(new Integer(serviceOrder.get(i)));
		}
		return result;
	}
	public static void insertNewService(int tenantid, String workFlowName, String newServiceName)
	{
		String strQuery = "select max(wsnumber) from workflowtable where (tenantid=1 or tenantid=" + tenantid;
		strQuery += ") and workflowname='" + workFlowName + "'";
		int maxSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
		strQuery = null;
		
		strQuery = "select webserviceid from webservicetable where (tenantid=1 or tenantid=" + tenantid + ") and ";
		strQuery += "webservicename='" + newServiceName + "'";
		int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
		
		String strInsert = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
		strInsert += maxSvcNum + 1 + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcId + ")";
		insertIntoDataBase(strInsert);
	}
	public static void deleteService(int tenantId, String workFlowName,int svcOrderNumber)
	{
		String strDelete = "delete from workflowtable where tenantid=" + tenantId;
		strDelete += " and workflowname='" + workFlowName + "' and wsnumber=" + svcOrderNumber;
		insertIntoDataBase(strDelete);
	}
	public static void insertNewWorkFlow(int tenantid, String workFlowName, Vector<String> webSvcNames)
	{
		String strQuery, strInsert = null;
		for(int i = 0; i < webSvcNames.size(); i ++)
		{
			strQuery = "select webserviceid from webservicetable where (tenantid=1 or tenantid=" + tenantid + ") and ";
			strQuery += "webservicename='" + webSvcNames.elementAt(i) + "'";
			int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = null;
			
			strInsert = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
			strInsert += i + 1 + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcId + ")";
			insertIntoDataBase(strInsert);
			strInsert = null;
		}
	}
	public static int getMaxSvcNum(int tenantid, String workFlowName)
	{
		String strQuery = "select max(wsnumber) from workflowtable where tenantid=" + tenantid;
		strQuery += " and workflowname='" + workFlowName + "'";
		int highestSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
		return highestSvcNum;
	}
	public static void changeSvcOrder(int tenantId, String workFlowName, Vector<String[]> newOrder)
	{
		String strQuery, strUpdate;
		for(int i = 0; i < newOrder.size(); i ++)
		{
			strQuery = "select webservicetable.webserviceid from webservicetable,workflowtable where webservicetable.tenantid=" + tenantId + " and ";
			strQuery += "webservicetable.webserviceid=workflowtable.webserviceid and webservicename='" + newOrder.get(i)[1] + "'";
			int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = null;
			
			strUpdate = "update workflowtable set wsnumber="+ newOrder.get(i)[0] +" where tenantid=" + tenantId;
			strUpdate += " and workflowname='" + workFlowName + "' and webserviceid=" + webSvcId;
			insertIntoDataBase(strUpdate);
			strUpdate = null;
		}
	}
}
